create proc [dbo].[p_TableSize10PointTrends](
						@DatabaseID bigint, 
						@StartDate datetime,
						@EndDate datetime) as  
set nocount on

declare @RangeHours int
set @RangeHours=datediff(hour,@StartDate,@EndDate)/10

declare @rangedates table([RangeStart] datetime,[RangeEnd] datetime)

declare @CurRange int
set @CurRange=0
while @CurRange<10
begin
	insert into @rangedates([RangeStart],[RangeEnd]) 
	select
		dateadd(hour,@RangeHours*@CurRange,@StartDate),
		dateadd(hour,@RangeHours*(@CurRange+1),@StartDate)

	set @CurRange=@CurRange+1
end

create table #y (
	[Name] varchar(100) not null,
	[TableID] bigint not null,
	[OwnerID] bigint not null,
	[P1_AvgSize] bigint null,
	[P2_AvgSize] bigint null,
	[P3_AvgSize] bigint null,
	[P4_AvgSize] bigint null,
	[P5_AvgSize] bigint null, 
	[P6_AvgSize] bigint null,
	[P7_AvgSize] bigint null,
	[P8_AvgSize] bigint null, 
	[P9_AvgSize] bigint null, 
	[P10_AvgSize] bigint null)

insert into #y (
	[Name],
	[TableID],
	[OwnerID])
select distinct
  	b.[Name],
	b.[ID],
  	b.[OwnerID]
from
	tDDTables b
where
	b.[DatabaseID]=@DatabaseID
	and b.[RevID]=
	(select top 1
		c.[RevID]
	 from
		tDDTables c
	 where
		c.[DatabaseID]=b.[DatabaseID]
		and c.[name]=b.[name]
	 order by		
		[DatabaseRevID] desc,
		[RevID] desc)

declare @RangeStart datetime,
	@RangeEnd datetime

declare @RangeData table(
			[Name] varchar(100),
			[OwnerID] bigint not null,
			[AvgSize] decimal(18,3))
			
set @CurRange=0

declare xcn cursor local forward_only for
select
	[RangeStart],
	[RangeEnd]
from
	@rangedates

open xcn

fetch next from xcn into @RangeStart,@RangeEnd

while @@fetch_status=0
begin
	delete from @RangeData

	insert into @RangeData(
		[Name],
		[OwnerID],
		[AvgSize]
	)
	select
  		b.[Name],
		b.[OwnerID],
  		sum(a.[Data] + a.[Index_size] + a.[Unused]) /	count(distinct a.[Session]) 'AvgSize'  
	from
  		tablesizehistory a
  			join tDDTables b on b.[ID]=a.[TableID]
  								and b.[RevID]=a.[TableRevID]  
	where  	
		a.[CreateDate] between @RangeStart and @RangeEnd
  		and b.[DatabaseID]=@DatabaseID  

	group by  	
		b.[Name],
  		b.[OwnerID]  
	order by
  		b.[Name],
  		b.[OwnerID] 

	if @CurRange=0
		update #y set
			[P1_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=1
		update #y set
			[P2_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=2
		update #y set
			[P3_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=3
		update #y set
			[P4_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=4
		update #y set
			[P4_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=5
		update #y set
			[P6_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=6
		update #y set
			[P7_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=7
		update #y set
			[P8_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=8
		update #y set
			[P9_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	else if @CurRange=9
		update #y set
			[P10_AvgSize]=a.[AvgSize]
		from
			@RangeData a
		where
			a.[name]=#y.[name]

	set @CurRange=@CurRange+1

	fetch next from xcn into @RangeStart,@RangeEnd
end
close xcn
deallocate xcn   

select 
	*  
from 
	#y a
order by
	a.[Name]

drop table #y